/**
 * 
 */
package org.compiere.report;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Properties;
import java.util.TimeZone;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.X_C_ElementValue;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.joda.time.DateTime;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

/**
 * @author V.Sokolov
 *
 */
public class ReportTurnoverBalanceSalary extends SvrProcess {

	/** Current context		*/
	private Properties m_ctx;
	/**	 */
	private ProcessInfo pi;
	/** */
	private String path = "";
	/** */
	Timestamp dateFrom;
	Timestamp dateTo;
	/** */
	int account_ID = 0;
	
	/* 
	 */
	@Override
	protected void prepare() {
		
		log.info("Prepared statement for report Turnover balance sheet");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)	{
			String name = para[i].getParameterName();
			if (name == null); 
				//
			else if (name.toLowerCase().endsWith("directory"))
				path = (String)para[i].getParameter();
			else if (name.compareTo("C_ElementValue_ID") == 0)
				account_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.compareTo("DateFrom") == 0)
				dateFrom = (Timestamp) para[i].getParameter();
			else if (name.compareTo("DateTo") == 0)
				dateTo = (Timestamp) para[i].getParameter();
			else
			{
				log.log(Level.INFO, "Unknown Parameter: " + name);
			}
		}
		
		if(path.isEmpty())
			path = Util.localFilePath;
		else
			path = path + System.getProperty("file.separator");
	}

	/* 
	 */
	@Override
	protected String doIt() throws Exception {

		//Types of cells for
		Label label;
		Number number;
		// There are checks for a specified pattern
		String templateName = "SampleBalanceSalary";
		//--
		X_C_ElementValue acc = new X_C_ElementValue(m_ctx, account_ID, get_TrxName());
		 String titleName = new Timestamp(System.currentTimeMillis()).toString();
		//--
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate")+" "+templateName);
				
		// We define a path to generate
		String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
		StringBuffer fullPath = new StringBuffer(path); 
		fullPath.append("Счет-"+acc.getValue()+"_"+titleName.substring(0, 10)+"_"+titleName.substring(11, 13)
	    		+"-"+titleName.substring(14, 16)).append(fileExtension);
				
		// 
		File templateCopy = new File(fullPath.toString());
		File temporaryFile = entry.getFile(Util.localFilePath+templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		 try {
			 tableBook = Workbook.getWorkbook(temporaryFile);
			 copy = Workbook.createWorkbook(templateCopy, tableBook);
			 sheet = copy.getSheet(0);
		} catch (Exception e) {
		  	ADialog.error(999, null, "Error tableBook. " + e.getMessage());
		   	return "Error tableBook.";
		}

		ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
		
		// Standart style cell
		WritableCellFormat borderStyle = new WritableCellFormat();
		borderStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		borderStyle.setAlignment(Alignment.CENTRE);
		borderStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		borderStyle.setWrap(true);
		// Department style cell
		WritableFont fontBP = new WritableFont (WritableFont.ARIAL, 11, WritableFont.NO_BOLD);
		WritableCellFormat bpname = new WritableCellFormat(fontBP);
		bpname.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		bpname.setAlignment(Alignment.LEFT);
		bpname.setVerticalAlignment(VerticalAlignment.CENTRE);
		bpname.setWrap(true);
		// Title style cell
		WritableFont fontTitle = new WritableFont (WritableFont.ARIAL, 12, WritableFont.BOLD);
		WritableCellFormat titleStyle = new WritableCellFormat(fontTitle);
		titleStyle.setAlignment(Alignment.CENTRE);
		titleStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		//
		WritableCellFormat numberStyle = new WritableCellFormat(NumberFormats.THOUSANDS_FLOAT);
		numberStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		numberStyle.setAlignment(Alignment.RIGHT);
		numberStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		numberStyle.setWrap(true);
		
		 //style for bold font
	       WritableFont arial10boldfont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD);
	       WritableCellFormat arial10boldformat = new WritableCellFormat(arial10boldfont);
	       arial10boldformat.setBorder(Border.LEFT, BorderLineStyle.MEDIUM);
	       arial10boldformat.setBorder(Border.TOP, BorderLineStyle.MEDIUM);
	       arial10boldformat.setBorder(Border.RIGHT, BorderLineStyle.THIN);
	       arial10boldformat.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
		//style for bold first colunm cells
	     //style for bold and #.## format number cells
	       WritableCellFormat twodpsFormat = new WritableCellFormat(arial10boldfont,NumberFormats.FLOAT); 
	       twodpsFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	       twodpsFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
	       twodpsFormat.setWrap(true);
		
		
		
		sheet.setRowView(1, 500); sheet.setRowView(2, 500);
//		DateTime dt = new DateTime(dateFrom.getTime());
//		Object[] param = new Object[]{Utils.getMonthName(dateFrom, Env.getAD_Language(m_ctx), false), Integer.toString(dt.getYear())};
//	    label = new Label(1, 2, sheet.getCell(1, 2).getContents()+MessageFormat.format(" за {0} месяц {1} года", param), titleStyle);
//	    sheet.addCell(label);
		SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");
		String strDate = dateFormat.format(dateFrom);
		String endDate = dateFormat.format(dateTo);
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT erps_periodstring(TO_DATE('" +strDate+"','DD.MM.YYYY'),TO_DATE('"+endDate+"','DD.MM.YYYY'))";
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				sheet.addCell(new Label(1,2,rs.getString(1),titleStyle));
			}
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally
		{
			DB.close(rs, pstmt);
		}
		
	    // Account
	    sheet.setRowView(5, 600);
	    label = new Label(1, 5, acc.getValue()+"-"+acc.getName(), bpname);
	    sheet.addCell(label);
		
		TurnoverBalance[] lines = getValues();
	    int row = cellStart.getR();
	    
	    int n = 0;
	    int num = 1;
	    int rowcount = 0;
	    BigDecimal totalDr1=new BigDecimal(0.0);
	    BigDecimal totalCr1=new BigDecimal(0.0);
	    BigDecimal totalDr2=new BigDecimal(0.0);
	    BigDecimal totalCr2=new BigDecimal(0.0);
	    BigDecimal totalDr3=new BigDecimal(0.0);
	    BigDecimal totalCr3=new BigDecimal(0.0);
		for(int j = row; j < (row+lines.length); j++){
			
			sheet.setRowView(j, 400);
			
			// #
	    	number = new Number(cellStart.getC(), j, num, borderStyle); 
	    	sheet.addCell(number);
			// initials
			label = new Label(cellStart.getC()+1, j, lines[n].getBpname(), bpname); 
			sheet.addCell(label);
			// Сальдо на начало
			number = new Number(cellStart.getC()+2, j, lines[n].getDr1().doubleValue(), numberStyle); 
			sheet.addCell(number);
			totalDr1 = totalDr1.add(lines[n].getDr1());
			number = new Number(cellStart.getC()+3, j, lines[n].getCr1().doubleValue(), numberStyle); 
			sheet.addCell(number);
			totalCr1 = totalCr1.add(lines[n].getCr1());
			// Обороты за период
			number = new Number(cellStart.getC()+4, j, lines[n].getDr2().doubleValue(), numberStyle); 
			sheet.addCell(number);
			totalDr2 = totalDr2.add(lines[n].getDr2());
			number = new Number(cellStart.getC()+5, j, lines[n].getCr2().doubleValue(), numberStyle); 
			sheet.addCell(number);
			totalCr2 = totalCr2.add(lines[n].getCr2());
			// Сальдо на конец
			number = new Number(cellStart.getC()+6, j, lines[n].getDr3().doubleValue(), numberStyle); 
			sheet.addCell(number);
			totalDr3 = totalDr3.add(lines[n].getDr3());
			number = new Number(cellStart.getC()+7, j, lines[n].getCr3().doubleValue(), numberStyle); 
			sheet.addCell(number);
			totalCr3 = totalCr3.add(lines[n].getCr3());
					
			n++;
			num++;
			rowcount++;
		}
		//add total cells
		sheet.addCell(new Label(cellStart.getC(),cellStart.getR()+rowcount," ",twodpsFormat));
		sheet.addCell(new Label(cellStart.getC()+1,cellStart.getR()+rowcount,"Итого ",twodpsFormat));
		sheet.addCell(new Number(cellStart.getC()+2,cellStart.getR()+rowcount,totalDr1.doubleValue(),twodpsFormat));
		sheet.addCell(new Number(cellStart.getC()+3,cellStart.getR()+rowcount,totalCr1.doubleValue(),twodpsFormat));
		sheet.addCell(new Number(cellStart.getC()+4,cellStart.getR()+rowcount,totalDr2.doubleValue(),twodpsFormat));
		sheet.addCell(new Number(cellStart.getC()+5,cellStart.getR()+rowcount,totalCr2.doubleValue(),twodpsFormat));
		sheet.addCell(new Number(cellStart.getC()+6,cellStart.getR()+rowcount,totalDr3.doubleValue(),twodpsFormat));
		sheet.addCell(new Number(cellStart.getC()+7,cellStart.getR()+rowcount,totalCr3.doubleValue(),twodpsFormat));
		//
		copy.write();
		copy.close();
		tableBook.close();
		temporaryFile.delete();
			       
		// Open the file with the application associated
		String fileName = fullPath.toString();
		Util.launchFile(new File(fileName));
		
		return Msg.translate(m_ctx, "Success");
	}
	
	private String getSql(){
		
		Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
		calendar.setTime(dateFrom);
		
		StringBuffer sql = new StringBuffer();
		
		sql.append(" WITH main AS (SELECT ad_client_id,ad_org_id,account_id,c_bpartner_id as subkonto1_id,\n "+
				   " SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2 \n "+
				   " FROM v_fi_fact_acct_with_subconto\n "+
				   " WHERE dateacct < " +DB.TO_DATE(dateFrom, true)+
				   " AND account_id = " + account_ID +" \n "+  
				   " GROUP BY ad_client_id,ad_org_id,account_id,c_bpartner_id UNION ALL\n "+
				   " SELECT ad_client_id,ad_org_id,account_id,c_bpartner_id as subkonto1_id,\n "+
				   " 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2\n "+ 
				   " FROM v_fi_fact_acct_with_subconto \n "+
				   " WHERE dateacct BETWEEN " + DB.TO_DATE(dateFrom, true)+
				   " AND " + DB.TO_DATE(dateTo, true)+
				   " AND account_id = "+ account_ID + " \n"+  
				   " GROUP BY ad_client_id,ad_org_id,account_id,c_bpartner_id)\n "+
				   " SELECT m.ad_client_id, m.ad_org_id, m.account_id, m.subkonto1_id, c.value as accvalue, bp.value as subvalue, bp.name as subname,\n "+				   
				   " adempiere.func1(SUM(dr1),SUM(cr1),'dr') as dr1,\n " +
				   " adempiere.func1(SUM(dr1),SUM(cr1),'cr') as cr1,\n " +
				   " SUM(dr2) as dr2, SUM(cr2) as cr2,\n "+
				   " adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as dr3, \n" +
				   " adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr') as cr3 \n "+
				   " FROM main m\n "+
				   " LEFT JOIN c_bpartner bp ON(m.subkonto1_id = bp.c_bpartner_id)\n "+
				   " LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id)\n "+
				   " WHERE m.ad_client_id = " + Env.getAD_Client_ID(m_ctx) + " AND m.ad_org_id = "+Env.getAD_Org_ID(m_ctx) + " AND bp.IsEmployee = 'Y' \n "+
				   " GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value,bp.value,bp.name\n " +
				   " ORDER BY bp.name ");
		
		return sql.toString();
	}
	
	private TurnoverBalance[] getValues(){
		
		TurnoverBalance values = new TurnoverBalance();
		List<TurnoverBalance> list = new ArrayList<TurnoverBalance>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
				
		try
		{
			pstmt = DB.prepareStatement(getSql().toString(),null);
			rs = pstmt.executeQuery();	
					
			while (rs.next ())
			{
				values = new TurnoverBalance();
				values.setAd_client_id(rs.getInt(1));
				values.setAd_org_id(rs.getInt(2));
				values.setAccount_id(rs.getInt(3));
				values.setAccvalue(rs.getString(5));
				values.setBpvalue(rs.getString(6));
				values.setBpname(rs.getString(7));
				values.setDr1(rs.getBigDecimal(8));
				values.setCr1(rs.getBigDecimal(9));
				values.setDr2(rs.getBigDecimal(10));
				values.setCr2(rs.getBigDecimal(11));
				values.setDr3(rs.getBigDecimal(12));
				values.setCr3(rs.getBigDecimal(13));
				list.add(values);
			}
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return list.toArray(new TurnoverBalance[list.size()]);
	}
	
	class TurnoverBalance{
		
		private int ad_client_id;
		private int ad_org_id;
		private int account_id;
		private String accvalue;
		private String bpvalue;
		private String bpname;
		private int department_id;
		private BigDecimal dr1;
		private BigDecimal cr1;
		private BigDecimal dr2;
		private BigDecimal cr2;
		private BigDecimal dr3;
		private BigDecimal cr3;
		
		public int getAd_client_id() {
			return ad_client_id;
		}
		public void setAd_client_id(int ad_client_id) {
			this.ad_client_id = ad_client_id;
		}
		public int getAd_org_id() {
			return ad_org_id;
		}
		public void setAd_org_id(int ad_org_id) {
			this.ad_org_id = ad_org_id;
		}
		public int getAccount_id() {
			return account_id;
		}
		public void setAccount_id(int account_id) {
			this.account_id = account_id;
		}
		public String getAccvalue() {
			return accvalue;
		}
		public void setAccvalue(String accvalue) {
			this.accvalue = accvalue;
		}
		public String getBpvalue() {
			return bpvalue;
		}
		public void setBpvalue(String bpvalue) {
			this.bpvalue = bpvalue;
		}
		public String getBpname() {
			return bpname;
		}
		public int getDepartment_id() {
			return department_id;
		}
		public void setDepartment_id(int department_id) {
			this.department_id = department_id;
		}
		public void setBpname(String bpname) {
			this.bpname = bpname;
		}
		public BigDecimal getDr1() {
			return dr1;
		}
		public void setDr1(BigDecimal dr1) {
			this.dr1 = dr1;
		}
		public BigDecimal getCr1() {
			return cr1;
		}
		public void setCr1(BigDecimal cr1) {
			this.cr1 = cr1;
		}
		public BigDecimal getDr2() {
			return dr2;
		}
		public void setDr2(BigDecimal dr2) {
			this.dr2 = dr2;
		}
		public BigDecimal getCr2() {
			return cr2;
		}
		public void setCr2(BigDecimal cr2) {
			this.cr2 = cr2;
		}
		public BigDecimal getDr3() {
			return dr3;
		}
		public void setDr3(BigDecimal dr3) {
			this.dr3 = dr3;
		}
		public BigDecimal getCr3() {
			return cr3;
		}
		public void setCr3(BigDecimal cr3) {
			this.cr3 = cr3;
		}		
		
	}

}
